Stored Procedure

存储过程

存储过程(Stored Procedure)是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率有好处，这些语句可以接受参数，并在需要时返回结果。存储过程常用于封装复杂的业务逻辑，简化应用程序的数据库交互。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点

封装功能

就像把一系列动作（比如做饭的步骤）打包成一个操作（做饭），存储过程把一系列数据库操作（查询、更新等）封装在一起，让你可以一次性调用，不需要每次都重复写。

可以传参数

就像给做饭的菜谱提供不同的食材，存储过程可以接受输入参数（给菜加不同的调料），也可以输出结果（做好的菜）。

提高效率

存储过程是在数据库中直接运行的，就像在厨房里直接烹饪，而不是把食材带到外面去做。这样能减少处理时间，让操作更快。

控制事务

存储过程能保证一系列操作要么都成功，要么都失败，就像做菜时要确保每一步都做对，否则就把菜丢掉，保证菜的质量。

错误处理

存储过程可以设置错误处理机制，就像遇到问题时有备用计划，比如如果食材不够，就可以用其他的代替。

预编译和优化

存储过程在第一次执行时会被“编译”和优化，像把常用的做饭步骤记住并加快速度，以后做饭时更快。

条件和循环控制

存储过程可以进行条件判断和循环，就像根据菜谱上的要求调整做饭的步骤，可以做出更复杂的菜。

减少重复工作

把重复的操作（比如每次都写的查询语句）放到存储过程中，这样应用程序只需要调用存储过程，减少了重复的工作。

确保数据一致性

存储过程可以帮助确保数据的一致性和完整性，就像做菜时确保每一步都做对，最终菜的质量才能有保证。

简化应用程序

存储过程可以将复杂的数据库操作封装起来，让应用程序代码更简单，就像把复杂的做饭过程简化成几个步骤。

版本管理

存储过程可以版本控制，就像跟踪菜谱的不同版本，确保每次做菜时都是最新的做法。

定时任务

存储过程可以设置定时自动执行，就像定期清理厨房，确保数据库的维护和数据清理自动进行。

缺点

1.架构不清晰，不够面向对象，存储过程不太适合面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，业务逻辑在存储层实现，增加了业务和存储的耦合，代码的可读性也会降低，

2.开发和维护要求比较高，存储过程的编写直接依赖于开发人员，如果业务逻辑改动较多，需要频繁直接操作数据库，大量业务降维到数据库，很多异常不能在代码中捕获，出现问题较难排查，需要数据库管理人员的帮助。

3.可移植性差，过多的使用存储过程会降低系统的移植性。在对存储进行相关扩展时，可能会增加一些额外的工作。

基础用法

创建

DELIMITER //
CREATE PROCEDURE procedure_name (IN param1 datatype, OUT param2 datatype, INOUT param3 datatype)
BEGIN
    -- SQL 语句
    -- 例如：SELECT, INSERT, UPDATE, DELETE 等
END //
DELIMITER;
例子:
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
    select count(*) from emp;
END //
DELIMITER;

1.DELIMITER：

用于更改语句的结束符。默认是 ;，但因为存储过程内部会使用 ; 作为 SQL 语句的结束符，所以需要临时更改结束符以避免混淆。通常我们会用 // 或 $$ 作为新的结束符。

2.CREATE PROCEDURE：

用来创建存储过程。procedure_name 是你要创建的存储过程的名称。

3.参数列表：

IN：输入参数，用于传递数据到存储过程。
OUT：输出参数，用于从存储过程返回数据。
INOUT：输入输出参数，既可以传递数据，也可以返回数据。
datatype：参数的数据类型。
param_name：参数名称。

4.BEGIN 和 END：

定义存储过程的主体部分，包含要执行的 SQL 语句。BEGIN 标记存储过程的开始，END 标记存储过程的结束。

5..SQL 语句：

存储过程内部可以包含任何有效的 SQL 语句，如 SELECT、INSERT、UPDATE、DELETE 等。

调用

CALL procedure_name(param1, param2, ...);
例子:call p1();

param1, param2, ...：传递给存储过程的参数值。

查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='XXX';
---查询指定数据库的存储过程及状态信息
例子:select * from information_schema.routines where routine_schema='index_mysql';

SHOW CREATE PROCEDURE 存储过程名称；
---查询某个存储过程的定义
例子:show create procedure p1;

删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;
例子:drop procedure if exists p1;

Warning

在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符，记得指定完后要将结束符换回去。

系统变量

类型

MySQL中根据变量的作用范围可以将其分为两种：

全局变量：影响整个数据库
会话变量：影响某个会话

大部分变量同时具有全局和会话两种作用范围，其中会话变量会在客户端连接到MySQL时会根据全局变量进行初始化。

而根据变量是否可以在数据库运行时动态修改，也可将变量分为两种：

静态变量：数据库运行时不能修改，需要重启生效
动态变量：可以数据库运行时修改，不需要重启

SHOW

使用show [global | session] variables [like ‘variable_name’];可以查看变量的值。

global和session关键字指定查看全局或会话变量，省略时默认为会话变量;
like ‘variable_name’ 子句查看指定变量的值，可以使用通配符%和_，省略like子句则会输出所有变量;

//使用show命令查看autocommit（自动提交）变量的值
mysql> show variables like 'autocommit';  ---查看默认变量时的autocommit，为OFF;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show session variables like 'autocommit';   ---查看session变量时的autocommit,为OFF;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'autocommit';   ---查看global变量时的autocommit,为ON;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Note

部分变量的范围只有全局，此时global/session关键字，无论用哪个关键字或省略，返回的都是全局变量值。

在不知道全部系统变量名称的情况下的最佳查询方法。

SELECT

使用select @@[global|session].variable_name; 可以查看指定变量的值，global/session省略时，默认查看会话变量。和show命令不同，select命令不能模糊匹配，只能查看某个具体变量的值。

查看autocommit变量的值：

mysql> select @@autocommit; -- 等于 select @@session.autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

使用

有参数的存储过程

这里的参数时 create procedure procedure_name([in / out / inout types]) 中的in / out / inout 参数，即输入参数，输出参数，既输入又输出参数，可以介入存储过程，看例子:

/* 1,'a';2,'b';3,'c';4,'d' (id,name)*/
create PROCEDURE p1(in score int,out result varchar(20))
begin 
  select name into result from emp where id = score+1;
END;
set @a = ' ';
call p1(1,@a);
select @a;
+------+
| @a   |
+------+
| b    |
+------+

当参数为IN时，即使数据变量在存储过程中发生改变，在存储过程结束后也不会改变，即IN中的变量类型为局部变量；

当参数为OUT时，不可以将参数作为一个运行的值使用，即使他有初始值，一旦运行，参数结果将为NULL；

当参数为INOUT时，既可以输入即进行运算，也可以将它输出；

输入仅输入，输出仅输出，相结合可以使用，但不太推荐；

条件IF语句

IF 语句包含多个条件判断,根据结果为 TRUE、FALSE执行语句,与编程语言中的 IF ELSE类似;

 create procedure p1(in week int)
    -> begin
    -> if week = 1 then   ---首个关键字为IF
    -> select '周一';
    -> elseif week = 2 then   ---其次为ELSEIF
    -> select '周二';
    -> elseif week = 3 then
    -> select '周三';
    -> else    ---最后一个为ELSE，一定要有
    -> select '不想写了';
    -> end if;   ---结束IF,必须要有
    -> end //
Query OK, 0 rows affected (0.00 sec)

IF语句与正常编程逻辑没有太多区别，唯一的注意点在于逻辑层面需要注意一下，其实本质无异；

条件CASE语句

case是另一个条件判断的语句,类似于编程语言中的 choose、when语法。MySQL 中的 case 语句有两种语法格式。

/* 第一种方法 */
create PROCEDURE p1(in s1 int,out r1 varchar(10))
BEGIN 
case    ---开始关键字case
when s1 < 10 then   
set r1 = '小于10';
when s1 >=60 then 
set r1 = '大于60';
when s1 < 60 and s1 >=10 then 
set r1 = '中间数';
end case;  ---结束case
end;

/* 第二种方法 */
create procedure p1(in s1 int,out r1 varchar(20))
BEGIN
case s1  ---关键字为case+要判断的参数 
  when 1 THEN
  set r1 := 'first';
  when 2 THEN
  set r1 := 'second';
  when 3 THEN
  set r1 := 'third';
  end case;
end;

两种方法的区别就在于，第一种可以是多种类判断，如范围，定量的判断；第二种只能将参数中的值对于一个值进行定量的判断，即等于或不等于；

循环WHILE语句

while是一个对于条件进行判断的循环语句,与编程中的while无异；

/*对于1到N的值进行累加*/
mysql> CREATE PROCEDURE index_while ( IN n INT ) BEGIN
    ->   DECLARE
    ->     sum INT DEFAULT 0;
    ->   WHILE
    ->       n > 0 DO
    ->       
    ->       SET sum := sum + n;
    ->     
    ->     SET n := n - 1;
    ->     
    ->   END WHILE;
    ->   SELECT
    ->     sum;
    ->   
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL index_while(10);
+------+
| sum  |
+------+
|   55 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

循环REPEAT ... UNTIL ...语句

REPEATE…UNTLL 语句的用法和 C中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。

mysql> CREATE PROCEDURE index_repeat ( IN n INT ) BEGIN
    ->   DECLARE
    ->     sum INT DEFAULT 0;
    ->   REPEAT    //开始repeat循环
    ->       
    ->       SET sum := sum + n;
    ->     
    ->     SET n := n - 1; 
    ->     UNTIL n <= 0 
    ->   END REPEAT;   // 直到达到条件退出
    ->   SELECT
    ->     sum;
    -> 
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL index_repeat(10);
+------+
| sum  |
+------+
|   55 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

循环LOOP语句

循环语句，用来重复执行某些语句。

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。

LEAVE 语句效果对于C中的break，用来终止循环；
ITERATE语句效果相当于C中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

mysql> CREATE PROCEDURE index_loop ( IN age INT ) BEGIN
    ->   DECLARE
    ->     count INT DEFAULT 0;
    ->   index_loop :
    ->   LOOP
    ->     IF
    ->       age > 100 THEN
    ->         LEAVE index_loop;
    ->       
    ->       ELSEIF age <= 0 THEN
    ->       LEAVE index_loop;
    ->       ELSE 
    ->         SET age := age + 10;
    ->       
    ->       SET count := count + 1;
    ->       ITERATE index_loop;
    ->       
    ->     END IF;
    ->     
    ->   END LOOP index_loop;
    ->   SELECT
    ->     age,
    ->     count;
    ->   
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> call index_loop(12);
+------+-------+
| age  | count |
+------+-------+
|  102 |     9 |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

WHILE,REPEAT,LOOP的区别

三种循环的处理逻辑：

WHILE：

条件满足的情况下，则执行循环体内容，不满足则结束循环； ————先判断，后执行；

REPEAT ... UNTIL ...

先执行循环内操作，再判断是否满足条件，满足，则结束循环； ————先执行，后判断;

LOOP

先执行循环内操作，再判断是否满足条件，满足，则结束循环； ————先执行，后判断。

上面三种循环语句中，WHILE与另外两种循环语句区别最为明显，主要问题在于REPEAT,LOOP的区别：

Important

当条件为false时，REPEAT循环也能执行一次，类似于 C语言中的do ... while 循环；而 WHILE、LOOP 循环无法执行。

也就是REPEAT 和 LOOP的区别是，REPEAT即使判断为FALSE，也会执行一次，而LOOP则不执行。

QUESTION：WHILE和LOOP都是在经判断语句判断后才可以提交执行，那WHILE和LOOP的区别呢，除了判断的顺序和关系外，它们在运行时的差别是什么？

游标

在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，而使用简单的 SELECT 语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。游标在部分资料中也被称为光标。

关系数据库管理系统实质是面向集合的，在 MySQL 中并没有一种描述表中单一记录的表达形式，除非使用 WHERE 子句来限制只有一条记录被选中。所以有时我们必须借助于游标来进行单条记录的数据处理。

一般通过游标定位到结果集的某一行进行数据修改。

MySQL 游标只能用于存储过程和函数。

游标要在声明处理程序之前被声明，而且变量和条件还必须在声明游标或处理程序之前被声明
使用游标一般要经历下面四个步骤:
1. 声明游标
2. 打开游标
3. 使用游标
4. 关闭游标

声明游标

DECLARE cursor_name CURSOR FOR select_statement;

cursor_name 为游标名称，select_statement表示SELECT语句，可以返回一行或多行数据；用一个查询语句来描述游标。

/*创建一个名结果集为emp表中id,name,age,sal的游标*/
DECLARE cur_emp CURSOR FOR
SELECT  id,name,age,sal FROM emp;

打开游标

声明游标之后，要想从游标中提取数据，必须首先打开游标；打开游标的时候，select语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。

OPEN cursor_name;

其中，cursor_name 表示所要打开游标的名称。需要注意的是，打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。

Caution

在程序中，一个游标可以打开多次。用户打开游标后，其他用户或程序可能正在更新数据表，所以有时会导致用户每次打开游标后，显示的结果都不同。

使用游标

FETCH cursor_name INTO var_name,....;

我们用一个游标来读取当前行，可以把数据保存到变量中，游标指针指到下一行
如果游标读取的数据有多个列，我们可以在into后面赋值给多个变量
var_name要在游标声明之前定义好
游标查询的结果集中的字段数必须要和into后面的变量数一样，不然在存储过程执行的时候会报错。

Note

MySQL 的游标是只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。

关闭游标

CLOSE cursor_name;

因为使用游标会占用系统资源，所以需要及时关闭，如果没有及时关闭，游标会一直保存到存储过程结束，影响系统运行的效率，关闭游标可以释放游标占用的系统资源。
关闭游标后，我们就不能再检索查询结果中的数据行，如果需要检索的话，就需要再次打开游标。

如果你不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭它。游标关闭之后，不能使用 FETCH 来使用该游标。

案例

根据传入参数usal,来查询用户表emp中，所有用户工资大于等于usal的用户姓名(name)和性别(gender)和工作岗位(dept_id)，并将这三种数据插入到新建的一张表中tb_user(id,name,gender,dept_id)中。

创建逻辑：

——A.声明游标，存储查询的结果集；

——B.创建表tb_user；

——C.开启游标；

——D.获取游标中的记录；

——E.插入数据到tb_user表中；

——F.关闭游标。

mysql> CREATE PROCEDURE p4 ( IN usal INT ) BEGIN
    ->   DECLARE
    ->     user_name VARCHAR ( 15 ) DEFAULT ' ';  //定义用户名
    ->   DECLARE
    ->     user_gen VARCHAR ( 3 ) DEFAULT ' ';    //定义用户性别
    ->   DECLARE
    ->     user_dept INT DEFAULT 0;       //定义用户职位
    ->   DECLARE
    ->     user_cur CURSOR FOR SELECT 
    ->     NAME,
    ->     gender,
    ->     dept_id 
    ->   FROM
    ->     emp 
    ->   WHERE
    ->     sal > usal;          //定义一个关于emp表中工资大于usal的name,gender,dept_id的结果集
    ->   CREATE TABLE tb_user ( 
        id INT PRIMARY KEY auto_increment,
        NAME VARCHAR ( 15 ) NOT NULL,
        gender VARCHAR ( 3 ) NOT NULL, dept_id INT NOT NULL
    ) COMMENT = 'THIS TABLE IS ABOUT SALARY MORE THAN SELECT_STATE';  //创建tb_user表
    ->   OPEN user_cur;
    ->   WHILE
    ->     TRUE DO		//因为游标中的结果集为多行数据，所以需要进行循环操作来提取数据；
    ->       FETCH user_cur INTO user_name,
    ->       user_gen,
    ->       user_dept;
    ->     INSERT INTO tb_user
    ->     VALUES
    ->       ( NULL, user_name, user_gen, user_dept );  //这里id为NULL是因为id可以自增
    ->   END WHILE;   //这里出现个问题，当游标中的数据全部提取完后，WHILE语句已经没有数据可以从游标中提取了，这时候怎么办呢?
    ->   CLOSE user_cur;          //要记住关，耗资源。
    ->   
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;   //此时库中没有tb_user表
+-----------------------+
| Tables_in_index_mysql |
+-----------------------+
| dept                  |
| emp                   |
+-----------------------+
2 rows in set (0.00 sec)

mysql> call p4(30000);   //执行存储过程p4
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed  //出现错误，表示没有数据可以获取了
mysql> show tables;   //表中出现了tb_user表
+-----------------------+
| Tables_in_index_mysql |
+-----------------------+
| dept                  |
| emp                   |
| tb_user               |
+-----------------------+
3 rows in set (0.00 sec)

mysql> select * from tb_user;   //tb_user表中的内容符合p4中的逻辑；
+----+---------+--------+---------+
| id | NAME    | gender | dept_id |
+----+---------+--------+---------+
|  1 | Alice   | F      |       1 |
|  2 | John    | M      |       2 |
|  3 | Miliy   | F      |       2 |
|  4 | Lucy    | F      |       2 |
|  5 | Poter   | M      |       2 |
|  6 | Amzie   | M      |       3 |
|  7 | Frilan  | M      |       3 |
|  8 | Capile  | M      |       3 |
|  9 | Horminy | F      |       3 |
+----+---------+--------+---------+
9 rows in set (0.00 sec)

Warning

既然p4运行的了，结果也没有问题，为什么会出错呢？

while中的判断为TRUE运行，非FAULSE都可以一直运行，那怎么阻止这种情况就需要运用到HANLDER;

条件处理程序(HANDLER)

条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

-- for后面跟上的就是条件，满足什么样的条件我才执行handler这个动作
-- statement：具体的SQL逻辑
DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement; 

handler_action 的取值：  
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
    
condition_value 的取值： 
    SQLSTATE  sqlstate_value: 状态码，如 02000 -- 当我们执行SQL的时候，它抛出的SQL语句的状态码来决定到底执行continue还是exit
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写，SQL警告
    NOT FOUND: 所有以02开头的SQLSTATE代码的简写，没有找到
    SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND 捕获的SQLSTATE代码的简写

以上一个存储函数为例：

CREATE PROCEDURE p4 ( IN usal INT ) BEGIN
  DECLARE
    user_name VARCHAR ( 15 ) DEFAULT ' ';
  DECLARE
    user_gen VARCHAR ( 3 ) DEFAULT ' ';
  DECLARE
    user_dept INT DEFAULT 0;
  DECLARE
    user_cur CURSOR FOR SELECT NAME
    ,
    gender,
    dept_id 
  FROM
    emp 
  WHERE
    sal > usal;
  DECLARE		<---主要在这，声明一个条件处理程序，当满足SQL状态码为02000时，触发退出操作并关闭游标。
    EXIT HANDLER FOR SQLSTATE '02000' CLOSE user_cur;
  DROP TABLE
  IF
    EXISTS tb_user;
  CREATE TABLE tb_user ( id INT PRIMARY KEY auto_increment, NAME VARCHAR ( 15 ) NOT NULL, gender VARCHAR ( 3 ) NOT NULL, dept_id INT NOT NULL ) COMMENT = 'THIS TABLE IS ABOUT SALARY MORE THAN SELECT_STATE';
  OPEN user_cur;
  WHILE
    TRUE DO
      FETCH user_cur INTO user_name,
      user_gen,
      user_dept;
    INSERT INTO tb_user
    VALUES
      ( NULL, user_name, user_gen, user_dept );
    
  END WHILE;
  CLOSE user_cur;
  
END $
mysql> CALL p4(20000);
Query OK, 0 rows affected (0.04 sec)

现在就不会出现报错了；

权限问题

DEFINER用于指明存储过程是由哪个用户定义的，默认存储过程的定义者是存储过程，跟存储过程的使用权限无关。
INVOKER用于指定哪些用户有调用存储过程的权限，此时会以调用者的权限去执行存储过程。
默认情况下被执行的存储过程具有其创建者的权限，比如用户A创建了存储过程P1，用户B运行存储过程P1时，只有用户A有操作权限的数据才能被P1操作。
假设用户A只有CREATE ROUTINE权限，没有select、update、delete等权限，正常情况下存储过程P1不能执行任何操作。如果在创建存储过程P1时，使用 SQL SECURITY INVOKER特征子句，让存储过程使用运行者的权限，这样即使P1的创建者没有数据操作权限，P1也可以正常使用。

算了，不想写了，就是DEFINER不需要操作该存储过程的人员拥有修改，删除，查看权限，只需要创建该存储过程的人有就好了，和你得拥有EXECUTE(执行)；而INVOKER需要执行人员拥有修改啊这些权限，这个权限的设定不是创建存储过程前面得DEFINER,而是后面的SQL SECURITY [INVOKER/DEFINER] ，前面得DEFINER是指定谁可以使用得。

总结

存储过程与SQL语句如何抉择？

架构设计没有绝对，只有在当前的场景下最合适的。

普通的项目开发中，不建议大量使用存储过程，对比SQL语句，存储过程适用于业务逻辑复杂，比较耗时，同时请求量较少的操作，例如后台大批量查询、定期更新等。

（1）当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时可以考虑应用存储过程

（2）在一个事务的完成需要很复杂的商业逻辑时可以考虑应用存储过程

（3）比较复杂的统计和汇总可以考虑应用后台存储过程。